<?php
namespace app\job;

use think\facade\Queue;
use think\facade\Db;
use think\exception\ValidateException;
use think\queue\Job;

class JobClassesTotal
{
    // 班级学习明细
    public function task1(Job $job, array $data)
    {

        $isJobDone = $this->doRecordJobTask1($data);

        if ($isJobDone['code'] == 0) {

            $job->delete();

            // 1.当前任务由哪个类来负责处理
            // 当轮到该任务时，系统将生成该类的实例，并调用其fire方法
            $jobHandlerClassName = 'app\job\JobClassesTotal@task1';

            // 2.当任务归属的队列名称，如果为新队列，会自动创建
            $jobQueueName = "ClassesTotalJobQueue";


            // 3.当前任务所需业务数据，不能为resource类型，其他类型最终将转化为json形式的字符串
            $jobData = ['ts' => $isJobDone['ts'], 'download_id' => $isJobDone['download_id'], 'page' => $isJobDone['page'], 'time' => $isJobDone['time'], 'time1' => $isJobDone['time1'], 'status' => $isJobDone['status'], 'courseid' => $isJobDone['courseid'], 'keys' => $isJobDone['keys']];

            // 4.将该任务推送到消息列表，等待对应的消费者去执行
            // 入队列，later延迟执行，单位秒，push立即执行
            Queue::push($jobHandlerClassName, $jobData, $jobQueueName);

        } elseif($isJobDone['code'] == 1) {
            $job->delete();
            Db::name('document')->where('id', '=', $data['download_id'])->update(['url' => '/csv/classes/ClassesRecord-'.date('YmdHis', $data['ts']).'.csv', 'complete_time'=>time()]);
        } else {
            if ($job->attempts() > 3){
                $job->delete();
            }

        }

    }


    /**
     * 根据消息中的数据进行实际的业务处理...
     * @param array $data
     * @return bool
     */
    protected function doRecordJobTask1(array $data)
    {
        $date = date('YmdHis', $data['ts']);
        $file_path = root_path() . "/public/csv/classes/ClassesRecord-".$date.".csv";


        if (!file_exists($file_path)) {
            $fp = fopen($file_path,'a');

            //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));

            //添加导出标题
            fputcsv($fp, ['班级名称','课程名称', '班级ID', '开课时间', '课程评价分数', '课程追评分数', '员工工号', '员工姓名', 'Division', '部门', '注册方式','签到日期','签到时间','开始学习时间','学习时长(S)','学习时长(H)']);
        } else {
            $fp = fopen($file_path,'a');

            //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
            fwrite($fp, chr(0xEF).chr(0xBB).chr(0xBF));
        }
        $where = [];

        if (!empty($data['status'])) {
            if ($data['status'] == 1) {
                $where[] = ['a.start', '>', time()];

            }elseif ($data['status'] == 2) {
                $where[] = ['a.start', '<=', time()];
                $where[] = ['a.end', '>=', time()];

            }elseif ($data['status'] == 3) {
                $where[] = ['a.end', '<', time()];
            }
        }

        if (!empty($data['time'])) {
            $t = explode(' - ', $data['time']);
            $where[] = ['a.start', 'between', [strtotime($t[0]), strtotime($t[1])+86399]];
        }

        if (!empty($data['time1'])) {
            $t1 = explode(' - ', $data['time1']);
            $where[] = ['a.end', 'between', [strtotime($t1[0]), strtotime($t1[1])+86399]];
        }

        if (!empty($data['keys'])) {
            $where[] = ['a.teacher_name|a.address|a.title|a.e_title|b.e_seminar', 'like', '%'.$data['keys'].'%'];
        }

        if (!empty($data['courseid'])) {
            $where[] = ['a.courseid', '=', $data['courseid']];
        }

        $where[] = ['a.status', 'in','1,2'];


        $ids = Db::table('o_classes')
            ->alias('a')
            ->join('o_course b','a.courseid=b.courseid', 'left')
            ->where($where)
            ->column('a.id');

        $subQuery1 = Db::table('o_classes_log')
            ->field('SUM(learning_time) as time, userid')
            ->group('userid')
            ->buildSql();

        $members = Db::name('classes_user')
            ->alias('a')
            ->join('o_members b', 'b.userid=a.userid', 'left')
            ->join('o_course c', 'c.courseid=a.courseid', 'left')
            ->join('o_classes d', 'd.id=a.classes_id', 'left')
            ->join($subQuery1.'e','e.userid=a.userid','left')
            ->whereIn('a.classes_id', $ids)
            ->where('b.status', '=', 1)
            ->field('a.userid, a.classes_id, d.start, a.type, b.name, b.division, b.department_name, c.seminar, c.courseid, d.title, e.time')
            ->page($data['page'], 10)
            ->select()
            ->toArray();

        if ($members) {
            foreach($members as &$v) {
                $inf = $v;

                $count = mb_strlen($inf['userid']);
                if ($count == 6) {
                    if (is_numeric($inf['userid'])) {
                        $userid = "'00".$inf['userid'];
                    } else {
                        $userid = $inf['userid'];
                    }
                } else {
                    $userid = $inf['userid'];
                }
                $class_log = Db::name('classes_log')
                    ->where('userid', '=', $inf['userid'])
                    ->where('classes_id', '=', $inf['classes_id'])
                    ->order('add_time', 'asc')
                    ->find();
                $sign_log = Db::name('classes_log')
                    ->where('userid', '=', $inf['userid'])
                    ->where('classes_id', '=', $inf['classes_id'])
                    ->where('resource_id', '=', 0)
                    ->find();



                // 线下班级评价内容
                $subject = Db::name('subject')
                    ->where('id', '=', 6)
                    ->find();

                // 做题人数或计分人数
                $members_total = 0;
                // 总分
                $total_points = 0;

                $option = Db::name('subject_option')
                    ->where('subject_id', '=', $subject['id'])
                    ->field('id as option_id, subject_id, title, letter')
                    ->select()
                    ->toArray();

                foreach ($option as &$val) {
                    if ($val['letter'] != 'A') {
                        $members_count = Db::name('subject_answer')
                            ->where('aid', '=', $inf['classes_id'])
                            ->where('subject_id', '=', $val['subject_id'])
                            ->where('answer', '=', $val['option_id'])
                            ->where('type', '=', 4)
                            ->where('test_type', '=', 1)
                            ->where('is_review', '=', 0)
                            ->count();
                        $members_total += $members_count;
                        $fraction = $this->fraction($val['letter']);
                        $total_points += $members_count*$fraction;
                    }
                }
                // 平均分
                $average = empty($total_points) ? number_format(0, 1) : number_format(($total_points/$members_total), 1);


                // 线下班级追评内容
                $subject1 = Db::name('subject')
                    ->where('id', '=', 13)
                    ->find();

                // 做题人数或计分人数
                $members_total1 = 0;
                // 总分
                $total_points1 = 0;

                $option = Db::name('subject_option')
                    ->where('subject_id', '=', $subject1['id'])
                    ->field('id as option_id, subject_id, title, letter')
                    ->select()
                    ->toArray();

                foreach ($option as &$val) {
                    if ($val['letter'] != 'A') {
                        $members_count = Db::name('subject_answer')
                            ->where('aid', '=', $inf['classes_id'])
                            ->where('subject_id', '=', $val['subject_id'])
                            ->where('answer', '=', $val['option_id'])
                            ->where('type', '=', 4)
                            ->where('test_type', '=', 1)
                            ->where('is_review', '=', 1)
                            ->count();
                        $members_total1 += $members_count;
                        $fraction = $this->fraction($val['letter']);
                        $total_points1 += $members_count*$fraction;
                    }
                }

                // 平均分
                $average1 = empty($total_points1) ? number_format(0, 1) : number_format(($total_points1/$members_total1), 1);

                $array = [
                    $inf['title'],
                    $inf['seminar'],
                    $inf['classes_id'],
                    date('Y-m-d H:i:s', $inf['start']),
                    $average,
                    $average1,
                    $userid,
                    $inf['name'],
                    $inf['division'],
                    $inf['department_name'],
                    $inf['type'] == 1 ? '后台导入':'现场注册',
                    isset($sign_log['qiandao']) ? date('Y-m-d', $sign_log['add_time']) : '未签到',
                    isset($sign_log['qiandao']) ? date('H:i:s', $sign_log['add_time']) : '未签到',
                    (isset($class_log) ? date('Y-m-d H:i:s', $class_log['add_time']) : ''),
                    $inf['time'],
                    number_format($inf['time']/3600, 2)
                ];

                fputcsv($fp, $array);
            }
            return ['code' => 0, 'ts' => $data['ts'], 'download_id' => $data['download_id'], 'page' => ($data['page'] + 1), 'time' => $data['time'], 'time1' => $data['time1'], 'status' => $data['status'], 'courseid' => $data['courseid'], 'keys' => $data['keys']];

        } else {

            return ['code' => 1];
        }
    }

    // 学习记录-班级课程学习明细
    public function task2(Job $job, array $data)
    {

        $isJobDone = $this->doRecordJobTask2($data);

        if ($isJobDone['code'] == 0) {

            $job->delete();

            // 1.当前任务由哪个类来负责处理
            // 当轮到该任务时，系统将生成该类的实例，并调用其fire方法
            $jobHandlerClassName = 'app\job\JobClassesTotal@task2';

            // 2.当任务归属的队列名称，如果为新队列，会自动创建
            $jobQueueName = "ClassesRecordJobQueue";


            // 3.当前任务所需业务数据，不能为resource类型，其他类型最终将转化为json形式的字符串
            $jobData = ['ts' => $isJobDone['ts'], 'download_id' => $isJobDone['download_id'], 'page' => $isJobDone['page'], 'key' => $isJobDone['key'], 'start' => $isJobDone['start'], 'end' => $isJobDone['end'], 'department' => $isJobDone['department']];

            // 4.将该任务推送到消息列表，等待对应的消费者去执行
            // 入队列，later延迟执行，单位秒，push立即执行
            Queue::push($jobHandlerClassName, $jobData, $jobQueueName);

        } elseif($isJobDone['code'] == 1) {
            $job->delete();
            Db::name('document')->where('id', '=', $data['download_id'])->update(['url' => '/csv/classes/ClassesRecordList-'.date('YmdHis', $data['ts']).'.csv', 'complete_time'=>time()]);
        } else {
            if ($job->attempts() > 3){
                $job->delete();
            }

        }

    }

    /**
     * 根据消息中的数据进行实际的业务处理...
     * @param array $data
     * @return bool
     */
    protected function doRecordJobTask2(array $data)
    {
        $date = date('YmdHis', $data['ts']);
        $file_path = root_path() . "/public/csv/classes/ClassesRecordList-" . $date . ".csv";


        if (!file_exists($file_path)) {
            $fp = fopen($file_path, 'a');

            //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
            fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));

            //添加导出标题
            fputcsv($fp, ['工号', '姓名', 'Division', '部门', '全部班级', '已结束', '已学完', '总学习时长(s)']);
        } else {
            $fp = fopen($file_path, 'a');

            //添加BOM头，以UTF8编码导出CSV文件，如果文件头未添加BOM头，打开会出现乱码。
            fwrite($fp, chr(0xEF) . chr(0xBB) . chr(0xBF));
        }
        $where = [];

        if (!empty($data['department'])) {
            $where[]  = ['', 'EXP', Db::raw("CONCAT(',',e.department,',') REGEXP concat(',(',replace('".$data['department']."',',','|'),'),')")];
        }

        if (!empty($data['key'])) {
            $where[] = ['e.name|e.userid', 'like', '%' . $data['key'] . '%'];
        }

        $where[] = ['e.status', '=', 1];


        $where1 = [];
        $where2 = [];
        $where3 = [];
        if (!empty($data['start']) && !empty($data['end'])) {
            $where1[] = ['a.add_time', 'between', [strtotime($data['start']), strtotime($data['end'])+86399]];
            $where2[] = ['a.add_time', 'between', [strtotime($data['start']), strtotime($data['end'])+86399]];
            $where3[] = ['a.add_time', 'between', [strtotime($data['start']), strtotime($data['end'])+86399]];
        }

        if (!empty($data['source'])) {
            $where3[] = ['a.source', '=', $data['source']];
        }

        $subQuery1 = Db::table('o_classes_user')
            ->alias('a')
            ->join('o_members b','a.userid=b.userid','left')
            ->field('COUNT(a.classes_id) as classes_total, a.userid')
            ->where($where1)
            ->where('b.status', '=', 1)
            ->group('a.userid')
            ->buildSql();

        $subQuery2 = Db::table('o_classes_user')
            ->alias('a')
            ->join('o_classes b', 'b.id=a.classes_id','left')
            ->join('o_members c','a.userid=c.userid','left')
            ->where('b.end', '<', time())
            ->where('c.status', '=', 1)
            ->field('COUNT(a.classes_id) as end_total, a.userid')
            ->group('a.userid')
            ->where($where2)
            ->buildSql();

        $subQuery3 = Db::table('o_classes_log')
            ->alias('a')
            ->join('o_members b','a.userid=b.userid','left')
            ->field('SUM(a.learning_time) as duration, a.userid')
            ->where($where3)
            ->where('b.status', '=', 1)
            ->group('a.userid')
            ->buildSql();

        $rows = Db::table('o_classes_user')
            ->alias('a')
            ->join($subQuery1.'b','a.userid=b.userid','left')
            ->join($subQuery2.'c','a.userid=c.userid','left')
            ->join($subQuery3.'d','a.userid=d.userid','left')
            ->join('o_members e','a.userid=e.userid','left')
            ->group('a.userid')
            ->where($where)
            ->page($data['page'], 10)
            ->order('d.duration', 'desc')
            ->field('e.name, e.userid, e.department_name, e.division, b.classes_total, c.end_total, d.duration')
            ->select()
            ->toArray();

        if ($rows) {
            foreach ($rows as &$v) {
                if (empty($v['classes_total'])) {
                    $v['classes_total'] = 0;
                }

                if (empty($v['duration'])) {
                    $v['duration'] = 0;
                }

                if (empty($v['end_total'])) {
                    $v['end_total'] = 0;
                }

                $class_id = Db::name('classes_user')
                    ->where('userid', '=', $v['userid'])
                    ->column('classes_id');

                foreach ($class_id as $key => $val) {

                    $resource = Db::name('classes_step_resource')
                        ->where('classes_id', '=', $val)
                        ->count();
                    $log = Db::name('classes_log')
                        ->where('classes_id', '=', $val)
                        ->where('is_finish', '=', 1)
                        ->where('userid', '=', $v['userid'])
                        ->where($where1)
                        ->group('resource_id')
                        ->count();
                    if ($resource != $log) {
                        unset($class_id[$key]);
                    }
                }
                $finished_total = count($class_id);

                $count = mb_strlen($v['userid']);
                if ($count == 6) {
                    if (is_numeric($v['userid'])) {
                        $userid = "'00".$v['userid'];
                    } else {
                        $userid = $v['userid'];
                    }
                } else {
                    $userid = $v['userid'];
                }

                $arr = [
                    $userid,
                    $v['name'],
                    $v['division'],
                    $v['department_name'],
                    $v['classes_total'],
                    $v['end_total'],
                    $finished_total,
                    $v['duration'],
                ];

                fputcsv($fp, $arr);
            }

            return ['code' => 0, 'ts' => $data['ts'], 'download_id' => $data['download_id'], 'page' => ($data['page'] + 1), 'start' => $data['start'], 'end' => $data['end'], 'key' => $data['key'], 'department' => $data['department']];
        } else {

            return ['code' => 1];
        }
    }



    protected function fraction($key)
    {
        $data = ['A' => 0, 'B' => 1, 'C' => 2, 'D' => 3, 'E' => 4, 'F' => 5, 'G' => 6, 'H' => 7, 'I' => 8, 'J' => 9, 'K' => 10];
        return $data[$key];
    }
}